package database;

import gestione_risorse_umane.DipendenteAvventizio;
import gestione_risorse_umane.DipendentePiantaStabile;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class PersonaleDatabase {

	public static void main(String[] args) throws ClassNotFoundException
	  {
	    // load the sqlite-JDBC driver using the current class loader
	    Class.forName("org.sqlite.JDBC");
	    
	    Connection connection = null;
	    try
	    {
	      // create a database connection
	      connection = DriverManager.getConnection("jdbc:sqlite:db/gestionePersonale.db");
	      Statement statement = connection.createStatement();
	      statement.setQueryTimeout(30);  // set timeout to 30 sec.
	      
	      //statement.executeUpdate("drop table if exists dipendenti_avventizi");
	      statement.executeUpdate("create table dipendenti (CF string, nome String, cognome String, sesso String, mansione String, idContratto int)");
	      statement.executeUpdate("insert into dipendenti values('MRSS','mario' , 'rossi', 'm', 'cameriere', )");
	      statement.executeUpdate("insert into dipendenti values('GPBC', 'giuseppe', 'bianchi', 'm', 'cuoco', )");
	      statement.executeUpdate("insert into dipendenti values('LCVS', 'lucia', 'vasca', 'f', 'hostess', )");
	      statement.executeUpdate("insert into dipendenti values('PTDR', 'pietro', 'duro', 'm', 'steward', )");
	      //statement.executeUpdate("insert into tariffario values('steward', 3, 6)");
	      //statement.executeUpdate("insert into tariffario values('hostess', 2, 4)");*/
	     // ResultSet rs = statement.executeQuery("select count(*) from dipendenti_avventizi where mansione = 'cameriere'");
	      //while(rs.next())
	      //{
	        // read the result set
	       /* System.out.println("dipendente = " + rs.getString("CF"));
	        System.out.println("mansione = " + rs.getString("mansione"));
	        System.out.println("tipoPagamento = " + rs.getString("tipoPagamento"));*/
	    	//System.out.println(rs.getInt(1));
	    	/*System.out.println(rs.getString(2));
	    	System.out.println(rs.getString(3));
	    	System.out.println(rs.getInt(4));
	    	System.out.println(rs.getInt(5));*/
	     // }
	     /* rs = statement.executeQuery("PRAGMA table_info(evento)");
	      while(rs.next()) {
	    	  System.out.println(rs.getString(2));
	      }*/
	    }
	    catch(SQLException e)
	    {
	      // if the error message is "out of memory", 
	      // it probably means no database file is found
	      System.err.println(e.getMessage());
	    }
	    finally
	    {
	      try
	      {
	        if(connection != null)
	          connection.close();
	      }
	      catch(SQLException e)
	      {
	        // connection close failed.
	        System.err.println(e);
	      }
	      
	      
	    }
	  }
    
    public void insertInTables(Object t){
    	Class.forName("org.sqlite.JDBC");
        
	    try {
	     // create a database connection
	     connection = DriverManager.getConnection("jdbc:sqlite:C:\\Users\\Agostino\\workspaceCatering\\Catering_Ago_Max\\db\\gestionePersonale.db");
	      
		Statement statement;
		try {
			statement = connection.createStatement();
			
			if(t instanceof DipendentePiantaStabile) {
				DipendentePiantaStabile d = (DipendentePiantaStabile) t;
				statement.executeUpdate("insert into dipendentiPiantaStabile values('"+ d.getCF()+"','"+d.getNome()+"', '" + d.getCognome()+"', '"+d.getSesso()+"', '"+d.getMansione()+"', "+d.getIdContratto()+","+d.getRetribuzione()+ ")");
			}
			else if(t instanceof DipendenteAvventizio) {
				DipendenteAvventizio d = (DipendenteAvventizio) t;
				statement.executeUpdate("insert into dipendentiPiantaStabile values('"+ d.getCF()+"','"+d.getNome()+"', '" + d.getCognome()+"', '"+d.getSesso()+"', '"+d.getMansione()+"', "+d.getIdContratto()+ ",'"+d.getTipoPagamento()+"')");
			}
			
	        statement.close();
		}
		catch(SQLException e) {
	        // connection close failed.
	        System.err.println(e);
	      }
	    }
}
    
    private Connection connection;
}
